from datetime import datetime

from sqlalchemy import func, select, exc, and_
from app.config.db import DBUtils
from app.model.invoiceInputModel import InvoiceInput
from app.model.invoiceOutputModel import InvoiceOutput


class InvoiceFlowService:
    def __init__(self):
        self.db_utils = DBUtils()
        # self.current_year = datetime.now().year

    # 需考虑内部交易情况
    def select_invoice_flow_info(self, session, company, is_cydw, year):
        combos = [
            {'id': 'cb3', 'label': '上游发票商品'},
            {'id': 'cb4', 'label': '下游发票商品'}
        ]
        # {'id': 'node1', 'label': '上游供应商', 'nodeType': 'wldw', 'combo': 'cb3'},
        # {'id': 'node3', 'label': '下游客户', 'nodeType': 'wldw', 'combo': 'cb4'}
        # if start_date and end_date:       #替换为年度
        #     condition_in =  InvoiceInput.YEAR.between(int(start_date.year), int(end_date.year))
        #     condition_out = InvoiceOutput.YEAR.between(int(start_date.year), int(end_date.year))
        # else:
        sk_edge = [{'source': 'cb4', 'target': 'node2', 'elx': 'SK'}]
        fk_edge = [{'source': 'node2', 'target': 'cb3', 'elx': 'FK'}]

        condition_in =  InvoiceInput.YEAR == year
        condition_out = InvoiceOutput.YEAR == year
        if int(is_cydw) == 1:
            company_node = [{'id': 'node2', 'label': company, 'nodeType': 'cydw'}]
            try:
                upstream_query = (
                    select(InvoiceInput.GOODS_NAME,
                           func.sum(InvoiceInput.FKJE).label('total'))
                    .where(and_(InvoiceInput.COMPANY_NAME == company,
                                condition_in))
                    .group_by(InvoiceInput.GOODS_NAME)
                )
                upstream_list = session.execute(upstream_query).fetchall()

                downstream_query = (
                    select(InvoiceOutput.GOODS_NAME,
                           func.sum(InvoiceOutput.FKJE).label('total'))
                    .where(and_(InvoiceOutput.SALER_NAME == company,
                                condition_out))
                    .group_by(InvoiceOutput.GOODS_NAME)
                )
                downstream_list = session.execute(downstream_query).fetchall()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
        else:
            company_node = [{'id': 'node2', 'label': company, 'nodeType': 'wldw'}]
            try:
                upstream_query = (
                    select(InvoiceOutput.GOODS_NAME,
                           func.sum(InvoiceOutput.FKJE).label('total'))
                    .where(and_(InvoiceOutput.BUYER_NAME == company,
                                condition_out))
                    .group_by(InvoiceOutput.GOODS_NAME)
                )
                upstream_list = session.execute(upstream_query).fetchall()

                downstream_query = (
                    select(InvoiceInput.GOODS_NAME,
                           func.sum(InvoiceInput.FKJE).label('total'))
                    .where(and_(InvoiceInput.SALER_NAME == company,
                                condition_in))
                    .group_by(InvoiceInput.GOODS_NAME)
                )
                downstream_list = session.execute(downstream_query).fetchall()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")

        if upstream_list:
            up_id = 1
            upstream_nodes = []
            upstream_edges = []
            for row in upstream_list:
                strid = 'nd' + str(up_id)
                if row.total >= 50 or row.total == 0:
                    str_total = str(round(row.total / 10000, 2))
                else:
                    str_total = '0.01'
                upstream_node = {
                    'id': strid,
                    'label': row.GOODS_NAME,
                    'nodeType': 'wpmc',
                    'amount': str_total,
                    'combo': 'cb3'}
                upstream_nodes.append(upstream_node)
                # upstream_edge = {
                #     'source': strid,
                #     'target': 'node1',
                #     'label': str_total ,
                #     'elx': 'wpmc'}
                # upstream_edges.append(upstream_edge)
                up_id += 1
            up_total = sum(float(row['amount']) for row in upstream_nodes)
            # up_total = sum(float(row['label']) for row in upstream_edges)
            fk_edge[0]['label'] = str(round(up_total / 10000, 2)) if up_total >=50 or up_total == 0 else '0.01'
        else:
            upstream_nodes = []
            upstream_edges = []
            fk_edge = [{'source': 'node2', 'target': 'cb3', 'elx': 'FK', 'label': 0}]

        if downstream_list:
            down_id = 1000
            downstream_nodes = []
            downstream_edges = []
            for row in downstream_list:
                strid = 'nd' + str(down_id)
                if row.total >= 50 or row.total == 0:
                    str_total = str(round(row.total / 10000, 2))
                else:
                    str_total = '0.01'
                downstream_node = {
                    'id': strid,
                    'label': row.GOODS_NAME,
                    'nodeType': 'wpmc',
                    'amount': str_total,
                    'combo': 'cb4'}
                downstream_nodes.append(downstream_node)
                # downstream_edge = {
                #     'source': strid,
                #     'target': 'node3',
                #     'label': str_total,
                #     'elx': 'wpmc'}
                # downstream_edges.append(downstream_edge)
                down_id += 1
            down_total = sum(float(row['amount']) for row in downstream_nodes)
            # down_total = sum(float(row['label']) for row in downstream_edges)
            sk_edge[0]['label'] = str(round(down_total / 10000, 2)) if down_total >=50 or down_total == 0 else '0.01'
        else:
            downstream_nodes = []
            downstream_edges = []
            sk_edge = [{'source': 'cb4', 'target': 'node2', 'elx': 'SK', 'label': 0}]

        nodes = upstream_nodes + downstream_nodes + company_node
        # edges = upstream_edges + downstream_edges + sk_edge + fk_edge
        edges = sk_edge + fk_edge
        result = [{
            'nodes': nodes,
            'edges': edges,
            'combos': combos,
        }]
        return result
